with tab as (
 select billcode,
        scansitecode,
        split(concat(scantime,'\001',
        coalesce(inputtime,''),'\001',
        coalesce(scanuser,''),'\001',
        coalesce(operatedate,''),'\001',
        coalesce(expresstype,''),'\001',
        coalesce(remark,''),'\001',
        coalesce(scansitecode,''),'\001',
        coalesce(remark2,'')
        ),'\001') as wid_columns
from jms_ods.tab_barscan_difficult      --21天内有效
where  dt BETWEEN date_add('{{ execution_date | cst_ds }}', -20) AND '{{ execution_date | cst_ds }}'
)

insert overwrite table jms_dwd.dwd_barscan_difficult_dt
select
billcode                                                 as billcode,
max(wid_columns)[6]                                      as scansitecode_later,
max(wid_columns)[0]                                      as scantime_later,
max(wid_columns)[1]                                      as inputtime_later,
max(wid_columns)[2]                                      as scanuser_later,
max(wid_columns)[3]                                      as operatedate_later,
max(wid_columns)[4]                                      as expresstype_later,
max(wid_columns)[5]                                      as remark_later,

min(wid_columns)[6]                                      as scansitecode_early,
min(wid_columns)[0]                                      as scantime_early,
min(wid_columns)[1]                                      as inputtime_early,
min(wid_columns)[2]                                      as scanuser_early,
min(wid_columns)[3]                                      as operatedate_early,
min(wid_columns)[4]                                      as expresstype_early,
min(wid_columns)[5]                                      as remark_early,

max(wid_columns)[7]                                      as remark2_later,  -- new add
min(wid_columns)[7]                                      as remark2_early,  -- new add
'billcode'                                               as dimtype,
date(max(wid_columns)[0])                                as dt
from tab
 group by billcode
having
    date(max(wid_columns)[0]) between date_add('{{ execution_date | cst_ds }}', -14) AND '{{ execution_date | cst_ds }}'

union all

select
billcode                                                 as billcode,
scansitecode                                             as scansitecode_later,
max(wid_columns)[0]                                      as scantime_later,
max(wid_columns)[1]                                      as inputtime_later,
max(wid_columns)[2]                                      as scanuser_later,
max(wid_columns)[3]                                      as operatedate_later,
max(wid_columns)[4]                                      as expresstype_later,
max(wid_columns)[5]                                      as remark_later,

scansitecode                                             as scansitecode_early,
min(wid_columns)[0]                                      as scantime_early,
min(wid_columns)[1]                                      as inputtime_early,
min(wid_columns)[2]                                      as scanuser_early,
min(wid_columns)[3]                                      as operatedate_early,
min(wid_columns)[4]                                      as expresstype_early,
min(wid_columns)[5]                                      as remark_early,

max(wid_columns)[7]                                      as remark2_later,  -- new add
min(wid_columns)[7]                                      as remark2_early,  -- new add

'bill_scansite'                                          as dimtype,
date(max(wid_columns)[0])                                as dt
from tab
 group by billcode, scansitecode
having
    date(max(wid_columns)[0]) between date_add('{{ execution_date | cst_ds }}', -14) AND '{{ execution_date | cst_ds }}';

